---
title: "sp73341_external_client_lookup stored procedure documentation"
summary: "Retrieved Oracle function that looks up an external clients email address from `SYSTEM_USER_CONTRACTOR`."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "WCIS_DBA"
source_name: "sp73341_external_client_lookup"
source_type: "FUNCTION"
retrieval_status: "found"
retrieval_command: "legends oracle query --name sp73341_external_client_lookup"
retrieval_date: "2026-08-25"
---
# sp73341_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Standalone function |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

## Purpose

`sp73341_external_client_lookup` is the Oracle replacement for the older SQL Server external-client lookup. It returns the matched contractors email address from `SYSTEM_USER_CONTRACTOR` when both mail ID and last name match.

## Procedures / Methods

### sp73341_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` |
| **Called by** | `clsTeamAssignment.InitializeEmailAddressUsingSystemUser` |
| **Description** | Returns the matched contractor email address or null when no contractor row qualifies. |

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_mailid` | IN | `VARCHAR2` | — | System user ID or mail ID to match. |
| `in_last_name` | IN | `VARCHAR2` | — | Last name to match. |

## Business Rules

The retrieved database source embeds the following server-side rules.

| Rule ID | Summary |
| --- | --- |
| [BR-VP-035](#br-vp-035-external-client-email-lookup-requires-trimmed-case-insensitive-matches-on-mail-id-and-last-name) | The function matches `SYSTEM_USER_CONTRACTOR` rows only when both `system_user_id` and `last_name` match the trimmed uppercase inputs. |
| [BR-VP-036](#br-vp-036-external-client-lookup-returns-null-when-no-contractor-row-is-found) | If the cursor fetch finds no row, the function explicitly returns null. |

### BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name

| Field | Value |
| --- | --- |
| **Statement** | The function selects an email address only from `SYSTEM_USER_CONTRACTOR` rows whose trimmed `LAST_NAME` and `SYSTEM_USER_ID` both match the trimmed uppercase input values. |
| **Trigger** | `sp73341_external_client_lookup` executes. |
| **Enforcement** | The cursor query filters with `upper(trim(last_name)) = upper(trim(in_last_name))` and `upper(trim(system_user_id)) = upper(trim(in_mailid))`. |
| **Outcome** | Only contractor rows matching both supplied identity values qualify. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` |

---

### BR-VP-036 External-client lookup returns null when no contractor row is found

| Field | Value |
| --- | --- |
| **Statement** | When the contractor cursor fetch does not find a row, the function assigns `v_email_address := null` before returning. |
| **Trigger** | `sp73341_external_client_lookup` executes and the cursor fetch is not found. |
| **Enforcement** | The body checks `if su_contractor_cur%notfound then v_email_address := null; end if;`. |
| **Outcome** | Callers receive null instead of an exception or stale email value when no contractor row matches. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` |

## Operations

The retrieved database source implements the following internal operations.

| Operation | Description | Trigger |
| --- | --- | --- |
| [Resolve contractor email address](#resolve-contractor-email-address) | Look up and return the trimmed contractor email address that matches the supplied mail ID and last name. | Caller invokes `sp73341_external_client_lookup`. |

### Resolve contractor email address

| Field | Value |
| --- | --- |
| **Description** | Look up and return the trimmed contractor email address that matches the supplied mail ID and last name. |
| **Trigger** | Caller invokes `sp73341_external_client_lookup`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` |

## Calling code references

| Class | Method | Source File |
| --- | --- | --- |
| `clsTeamAssignment` | `InitializeEmailAddressUsingSystemUser` | `SAIF.Business.View.Policy\clsTeamAssignment.vb` |

## Source Code

<details>
<summary>PL/SQL function source</summary>

```sql
FUNCTION sp73341_external_client_lookup
/********************************************************
* Procedure Title      : sp73341_external_client_lookup
* Name                  : sp73341_external_client_lookup
* Prepared By        : valrey
* Date Created      : 08/29/2013
*       This is an oracle replacement of sp_external_client_lookup.  The source is now going to be
*       system_user_contractor vs. net_registrant.
*/
(
   in_mailid in varchar2,
   in_last_name in varchar2)

   return varchar2
  is

    v_email_address     varchar2(60);

    cursor su_contractor_cur is
        select trim(email_address)
          from system_user_contractor
        where upper(trim(last_name)) = upper(trim(in_last_name))
            and upper(trim(system_user_id)) = upper(trim(in_mailid));


begin

    open su_contractor_cur;
    fetch su_contractor_cur into v_email_address;
    if su_contractor_cur%notfound then
        v_email_address := null;
    end if;
    close su_contractor_cur;

   return  v_email_address;


end sp73341_external_client_lookup;
```

</details>

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json`
- Calling code: `SAIF.Business.View.Policy\clsTeamAssignment.vb`